<?php

header('Content-Type: text/html; charset=utf-8');

class Admin_FunreportController extends Zend_Controller_Action
{
    public $PUL_ID = 1;
    
    public function porcentagemgeralAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();
        
        Zend_Loader::loadClass('tools','library');
        
        $db = Zend_Db_Table_Abstract::getDefaultAdapter(); 
        
        
        $query = 'SELECT p.DCP_ID, d.DCP_NOM' 
                .' FROM PUBLICACAO_LISTA_GABARITO p, DISCIPLINA d'
                .' WHERE  p.DCP_ID = d.DCP_ID AND PUL_ID = '.$this->PUL_ID
                .' GROUP BY p.DCP_ID';
        $disc = $db->fetchAll($query);
        foreach($disc as $dis){
            $qtd_acerto = 0;
            $query = 'SELECT SQL_CACHE r.PLR_ALT, g.PLG_ALT '
                    . 'FROM PUBLICACAO_LISTA_RESPOSTA r,  PUBLICACAO_LISTA_GABARITO g'
                .' WHERE r.PLG_ID = g.PLG_ID AND r.PUL_ID = g.PUL_ID '
                .' AND r.DCP_ID = g.DCP_ID '
                . 'AND r.DCP_ID = '.$dis['DCP_ID'].' AND g.DCP_ID = '.$dis['DCP_ID']
                .' AND r.PUL_ID = '.$this->PUL_ID;
            $resp = $db->fetchAll($query);
            foreach($resp as $res){
                if($res['PLR_ALT'] == $res['PLG_ALT']){
                    $qtd_acerto++;
                }                                
            }
            
            // total de questões por disciplina
            $query = 'SELECT count(*) as TOTAL ' 
                    .' FROM PUBLICACAO_LISTA_RESPOSTA '
                    .' WHERE DCP_ID = '.$dis['DCP_ID'].' AND PUL_ID = '.$this->PUL_ID;
            $tot = $db->fetchRow($query);
            
            
            $porc = ($qtd_acerto * 100) / $tot['TOTAL'];
            $porc = tools::trunc_valor($porc);
            echo $dis['DCP_NOM'].' - '.$porc.'% | Respostas: '.$tot['TOTAL'].', Acertos:'.$qtd_acerto;
            echo '<br>';
        }
                        
    }
    
    public function porcentagemporquestaoAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();
        
        Zend_Loader::loadClass('tools','library');
        
        $db = Zend_Db_Table_Abstract::getDefaultAdapter(); 
        
        $query = 'SELECT SQL_CACHE p.DCP_ID,d.DCP_NOM ' 
                .' FROM PUBLICACAO_LISTA_GABARITO p, DISCIPLINA d '
                .' WHERE p.DCP_ID = d.DCP_ID AND p.PUL_ID = '.$this->PUL_ID.' GROUP BY p.DCP_ID ORDER BY p.PLG_ID';
        $disc = $db->fetchAll($query);
        foreach($disc as $dis){
        
            $query = 'SELECT SQL_CACHE * ' 
                    .' FROM PUBLICACAO_LISTA_GABARITO '
                    .' WHERE DCP_ID = '.$dis['DCP_ID'].' AND PUL_ID = '.$this->PUL_ID;
            $gabr = $db->fetchAll($query);
            foreach($gabr as $gab){
                $qtd_acerto = 0;
                $query = 'SELECT SQL_CACHE r.PLR_ALT, g.PLG_ALT '
                        . 'FROM PUBLICACAO_LISTA_RESPOSTA r,  PUBLICACAO_LISTA_GABARITO g'
                    .' WHERE r.PLG_ID = g.PLG_ID AND r.PUL_ID = g.PUL_ID '
                    .' AND r.DCP_ID = g.DCP_ID '
                    . 'AND r.DCP_ID = '.$gab['DCP_ID'].' AND g.DCP_ID = '.$gab['DCP_ID']
                    .' AND r.PUL_ID = '.$this->PUL_ID.' AND r.PLR_QST = g.PLG_QST '
                    .' AND r.PLR_QST = '.$gab['PLG_QST'].'';
                $resp = $db->fetchAll($query);
                foreach($resp as $res){
                    if($res['PLR_ALT'] == $res['PLG_ALT']){
                        $qtd_acerto++;
                    }                                
                }

                // total de questões por disciplina
                $query = 'SELECT count(*) as TOTAL ' 
                        .' FROM PUBLICACAO_LISTA_RESPOSTA '
                        .' WHERE PLR_QST = '.$gab['PLG_QST'].' AND DCP_ID = '.$gab['DCP_ID'].' AND PUL_ID = '.$this->PUL_ID;
                $tot = $db->fetchRow($query);


                $porc = ($qtd_acerto * 100) / $tot['TOTAL'];
                $porc = tools::trunc_valor($porc);

                $porc = str_pad($porc, 5, '0', STR_PAD_LEFT);
                $qtd_acerto = str_pad($qtd_acerto, 5, '0', STR_PAD_LEFT);
                $tot['TOTAL'] = str_pad($tot['TOTAL'], 5, '0', STR_PAD_LEFT);

                echo $dis['DCP_NOM'].' '.$gab['PLG_QST'].' - '.$porc.'% | Respostas: '.$tot['TOTAL'].', Acertos: '.$qtd_acerto;
                echo '<br>';
            } 
        }          
    }
    
    public function alunossemrespotaAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();
        
        Zend_Loader::loadClass('tools','library');
        
        $db = Zend_Db_Table_Abstract::getDefaultAdapter();
            
        $query = 'SELECT SQL_CACHE pa.*, a.ALU_NOM ' 
                .' FROM PUBLICACAO_LISTA_ALUNO pa, ALUNO a'
                .' WHERE pa.ALU_ID = a.ALU_ID AND PLA_STA = 0'
                .' ORDER BY a.ALU_NOM ASC';
        $rows = $db->fetchAll($query);
        foreach($rows as $row){
            echo $row['ALU_NOM'];
            echo '<br>';
        }
    }
    
    ///// gerar relatórios
    
    public function questaodisciplina($PUL_ID) {
        
        Zend_Loader::loadClass('tools','library');
        
        $db = Zend_Db_Table_Abstract::getDefaultAdapter();
        
        $query = 'SELECT SQL_CACHE PUL_ID' 
                .' FROM PUBLICACAO_LISTA'
                .' WHERE PUL_STA = 2 AND PUL_STARELQSTDIS = 0 AND PUL_ID = '.$PUL_ID;
        $result = $db->fetchAll($query);
        foreach($result as $row){
            $query = 'SELECT SQL_CACHE p.DCP_ID, p.PUL_ID  ' 
                    .' FROM PUBLICACAO_LISTA_GABARITO p '
                    .' WHERE p.PUL_ID = '.$row['PUL_ID']
                    .' GROUP BY p.DCP_ID ORDER BY p.PLG_ID';
            $disc = $db->fetchAll($query);
            foreach($disc as $dis){
                $query = 'SELECT SQL_CACHE * ' 
                        .' FROM PUBLICACAO_LISTA_GABARITO '
                        .' WHERE DCP_ID = '.$dis['DCP_ID'].' AND PUL_ID = '.$dis['PUL_ID'];
                $gabr = $db->fetchAll($query);
                foreach($gabr as $gab){
                    $qtd_acerto = 0;
                    $query = 'SELECT SQL_CACHE r.PLR_ALT, g.PLG_ALT '
                            . 'FROM PUBLICACAO_LISTA_RESPOSTA r,  PUBLICACAO_LISTA_GABARITO g'
                        .' WHERE r.PLG_ID = g.PLG_ID AND r.PUL_ID = g.PUL_ID '
                        .' AND r.DCP_ID = g.DCP_ID '
                        . 'AND r.DCP_ID = '.$gab['DCP_ID'].' AND g.DCP_ID = '.$gab['DCP_ID']
                        .' AND r.PUL_ID = '.$dis['PUL_ID'].' AND r.PLR_QST = g.PLG_QST '
                        .' AND r.PLR_QST = '.$gab['PLG_QST'].'';
                    $resp = $db->fetchAll($query);
                    foreach($resp as $res){
                        if($res['PLR_ALT'] == $res['PLG_ALT']){
                            $qtd_acerto++;
                        }                                
                    }

                    // total de questões por disciplina
                    
                    $query = 'SELECT count(*) as TOTAL ' 
                            .' FROM PUBLICACAO_LISTA_RESPOSTA '
                            .' WHERE PLR_QST = '.$gab['PLG_QST'].' '
                            .' AND DCP_ID = '.$gab['DCP_ID'].' '
                            .' AND PUL_ID = '.$dis['PUL_ID'];
                    $tot = $db->fetchRow($query);
                     

                    if($tot['TOTAL'] > 0){
                        $porc = ($qtd_acerto * 100) / $tot['TOTAL'];
                        $porc = tools::trunc_valor($porc);
                    }

                    $porc = tools::str_pad_left($porc);
                    $qtd_acerto = tools::str_pad_left($qtd_acerto); 
                    $tot['TOTAL'] = tools::str_pad_left($tot['TOTAL']);

                    $arr = array(
                        'PUL_ID'    => $dis['PUL_ID'],
                        'DCP_ID'    => $dis['DCP_ID'],
                        'PQD_QST'   => $gab['PLG_QST'],
                        'PQD_POR'   => $porc,
                        'PQD_TOT'   => $tot['TOTAL'],
                        'PQD_ACR'   => $qtd_acerto,
                    );
                    
                    try {
                        $db->insert('PUBLST_QSTDIS', $arr);
                    } catch (Exception $ex) {

                    }

                }
            }
            
            $db->update('PUBLICACAO_LISTA', array('PUL_STARELQSTDIS' => 1), 'PUL_ID = '.$row['PUL_ID']);
            
        }
        //echo 'Gerado com sucesso.';
        
    }
    
    public function rankaluno($PUL_ID) {
        
        Zend_Loader::loadClass('tools','library');
        
        $db = Zend_Db_Table_Abstract::getDefaultAdapter();
        
        $query = 'SELECT SQL_CACHE PUL_ID' 
                .' FROM PUBLICACAO_LISTA'
                .' WHERE PUL_STA = 2 AND PUL_STARELRANALU = 0 AND PUL_ID = '.$PUL_ID;
        $result = $db->fetchAll($query);
        foreach($result as $row){
            $query = 'SELECT SQL_CACHE p.* ' 
                    .' FROM PUBLICACAO_LISTA_ALUNO p '
                    .' WHERE p.PUL_ID = '.$row['PUL_ID'];
            $alunos = $db->fetchAll($query);
            foreach($alunos as $alu){  
                $qtd_acerto = 0;
                // 180 linas
                $query = 'SELECT SQL_CACHE * ' 
                        .' FROM PUBLICACAO_LISTA_GABARITO '
                        .' WHERE PUL_ID = '.$alu['PUL_ID'];
                $gabr = $db->fetchAll($query);
                foreach($gabr as $gab){
                    
                    $query = 'SELECT SQL_CACHE r.PLR_ALT, g.PLG_ALT '
                            . 'FROM PUBLICACAO_LISTA_RESPOSTA r,  PUBLICACAO_LISTA_GABARITO g'
                        .' WHERE r.PLG_ID = g.PLG_ID AND r.PUL_ID = g.PUL_ID '
                        . 'AND r.ALU_ID = '.$alu['ALU_ID'].' '
                        .' AND r.PUL_ID = '.$alu['PUL_ID'].' AND r.PLR_QST = g.PLG_QST '
                        .' AND r.PLR_QST = '.$gab['PLG_QST'].'';
                    $resp = $db->fetchAll($query);
                    foreach($resp as $res){
                        if($res['PLR_ALT'] == $res['PLG_ALT']){
                            $qtd_acerto++;
                        }                                
                    }
                }

                // total de questões por disciplina
                /*
                $query = 'SELECT count(*) as TOTAL ' 
                        .' FROM PUBLICACAO_LISTA_RESPOSTA '
                        .' WHERE ALU_ID	 = '.$alu['ALU_ID'].' '
                        .' AND PUL_ID = '.$alu['PUL_ID'];
                */
                $query = 'SELECT count(*) as TOTAL ' 
                        .' FROM PUBLICACAO_LISTA_GABARITO '
                        .' WHERE PUL_ID = '.$alu['PUL_ID'];
                $tot = $db->fetchRow($query);

                $porc = 0;
                if($tot['TOTAL'] > 0){
                    $porc = ($qtd_acerto * 100) / $tot['TOTAL'];
                    $porc = tools::trunc_valor($porc);
                }

                $porc = tools::str_pad_left($porc);
                $qtd_acerto = tools::str_pad_left($qtd_acerto); 
                $tot['TOTAL'] = tools::str_pad_left($tot['TOTAL']);

                $qtd_acerto = tools::str_pad_left($qtd_acerto); 

                $arr = array(
                    'PUL_ID'    => $alu['PUL_ID'],
                    'ALU_ID'    => $alu['ALU_ID'],
                    'PLA_POR'   => $porc,
                    'PLA_TOT'   => $tot['TOTAL'],
                    'PLA_ACR'   => $qtd_acerto,
                );
                
                try {
                    $db->insert('PUBLST_RANALU', $arr);
                } catch (Exception $ex) {
                
                }
                
            }
            
            // rank alunos
            $query = 'SELECT SQL_CACHE PUL_ID,ALU_ID  ' 
                    .' FROM PUBLST_RANALU '
                    .' WHERE PUL_ID = '.$row['PUL_ID']
                    .' ORDER BY PLA_ACR DESC, ALU_ID, PUL_ID';
            $result2 = $db->fetchAll($query);
            foreach($result2 as $key => $row2){
                $db->update('PUBLST_RANALU', array('PLA_POS' => ($key + 1)), 'PUL_ID = '.$row2['PUL_ID'].' AND ALU_ID = '.$row2['ALU_ID']);
            }
            
            $db->update('PUBLICACAO_LISTA', array('PUL_STARELRANALU' => 1), 'PUL_ID = '.$row['PUL_ID']);
        }
        //echo 'Gerado com sucesso.';
    }
    
    public function questaodisciplinaaluno($PUL_ID) {
        
        Zend_Loader::loadClass('tools','library');
        
        $db = Zend_Db_Table_Abstract::getDefaultAdapter();
        
        $query = 'SELECT SQL_CACHE PUL_ID' 
                .' FROM PUBLICACAO_LISTA'
                .' WHERE PUL_STA = 2 AND PUL_STARELQSTDISALU = 0 AND PUL_ID = '.$PUL_ID;
        $result = $db->fetchAll($query);
        foreach($result as $row){
            $query = 'SELECT SQL_CACHE p.PUL_ID, p.ALU_ID ' 
                    .' FROM PUBLICACAO_LISTA_ALUNO p '
                    .' WHERE p.PUL_ID = '.$row['PUL_ID'];
            $alunos = $db->fetchAll($query);
            foreach($alunos as $alu){
                // 180 linas
                $query = 'SELECT SQL_CACHE DCP_ID' 
                        .' FROM PUBLICACAO_LISTA_GABARITO '
                        .' WHERE PUL_ID = '.$alu['PUL_ID']
                        .' GROUP BY DCP_ID';
                $gabr = $db->fetchAll($query);
                foreach($gabr as $gab){
                    $qtd_acerto = 0;
                    $query = 'SELECT SQL_CACHE r.PLR_ALT, g.PLG_ALT '
                            . 'FROM PUBLICACAO_LISTA_RESPOSTA r,  PUBLICACAO_LISTA_GABARITO g'
                            .' WHERE r.PLG_ID = g.PLG_ID AND r.PUL_ID = g.PUL_ID '
                            .' AND r.PLR_QST = g.PLG_QST AND r.DCP_ID = g.DCP_ID '
                            . 'AND r.ALU_ID = '.$alu['ALU_ID']
                            .' AND r.PUL_ID = '.$alu['PUL_ID']
                            .' AND r.DCP_ID  = '.$gab['DCP_ID'];
                    $resp = $db->fetchAll($query);
                    foreach($resp as $res){
                        if($res['PLR_ALT'] == $res['PLG_ALT']){ 
                            $qtd_acerto++;
                        }                                
                    }

                    // total de questões por disciplina
                    /*
                    $query = 'SELECT count(*) as TOTAL ' 
                            .' FROM PUBLICACAO_LISTA_RESPOSTA '
                            .' WHERE ALU_ID = '.$alu['ALU_ID']
                            .' AND PUL_ID = '.$alu['PUL_ID']
                            .' AND DCP_ID = '.$gab['DCP_ID'];
                    */
                    $query = 'SELECT count(*) as TOTAL ' 
                            .' FROM PUBLICACAO_LISTA_GABARITO '
                            .' WHERE PUL_ID = '.$alu['PUL_ID']
                            .' AND DCP_ID = '.$gab['DCP_ID'];
                    $tot = $db->fetchRow($query);
                    
                    
                    $porc = 0;
                    if($tot['TOTAL'] > 0){
                        $porc = ($qtd_acerto * 100) / $tot['TOTAL'];
                        $porc = tools::trunc_valor($porc);
                    }

                    $porc = tools::str_pad_left($porc);
                    $qtd_acerto = tools::str_pad_left($qtd_acerto); 
                    $tot['TOTAL'] = tools::str_pad_left($tot['TOTAL']);

                    $arr = array(
                        'PUL_ID'    => $alu['PUL_ID'],
                        'ALU_ID'    => $alu['ALU_ID'],
                        'DCP_ID'    => $gab['DCP_ID'],
                        'PQD_POR'   => $porc,
                        'PQD_TOT'   => $tot['TOTAL'],
                        'PQD_ACR'   => $qtd_acerto,
                    );

                    try {
                        $db->insert('PUBLST_QSTDISALU', $arr);
                    } catch (Exception $ex) {
                        
                    }
                }

            }
            
            $db->update('PUBLICACAO_LISTA', array('PUL_STARELQSTDISALU' => 1), 'PUL_ID = '.$row['PUL_ID']);
        }
        //echo 'Gerado com sucesso.';
    }
    
    
    public function questaodisciplinacurso($PUL_ID) {
        
        Zend_Loader::loadClass('tools','library');
        
        $db = Zend_Db_Table_Abstract::getDefaultAdapter();
        
        $query = 'SELECT SQL_CACHE PUL_ID' 
                .' FROM PUBLICACAO_LISTA'
                .' WHERE PUL_STA = 2 AND PUL_STARELQSTDISCUR = 0 AND PUL_ID = '.$PUL_ID;
        $result = $db->fetchAll($query);
        foreach($result as $row){
            $query = 'SELECT SQL_CACHE p.PUL_ID, m.ALU_ID , m.CUR_ID ' 
                    .' FROM PUBLICACAO_LISTA_ALUNO p, MATRICULA m '
                    .' WHERE p.ALU_ID = m.ALU_ID AND m.MAT_STA = 1 '
                    .' AND p.PUL_ID = '.$PUL_ID
                    .' GROUP BY m.CUR_ID ';
            $alunos = $db->fetchAll($query);
            foreach($alunos as $alu){
                // 180 linas
                $query = 'SELECT SQL_CACHE DCP_ID' 
                        .' FROM PUBLICACAO_LISTA_GABARITO '
                        .' WHERE PUL_ID = '.$alu['PUL_ID']
                        .' GROUP BY DCP_ID';
                $gabr = $db->fetchAll($query);
                foreach($gabr as $gab){
                    $qtd_acerto = 0;
                    $query = 'SELECT SQL_CACHE r.PLR_ALT, g.PLG_ALT '
                            . 'FROM PUBLICACAO_LISTA_RESPOSTA r,  PUBLICACAO_LISTA_GABARITO g, MATRICULA m '
                            .' WHERE r.PLG_ID = g.PLG_ID AND r.PUL_ID = g.PUL_ID '
                            .' AND r.PLR_QST = g.PLG_QST '
                            . 'AND r.ALU_ID = m.ALU_ID AND m.MAT_STA = 1 AND m.CUR_ID = '.$alu['CUR_ID']
                            .' AND r.PUL_ID = '.$alu['PUL_ID']
                            .' AND r.DCP_ID  = '.$gab['DCP_ID'];
                    $resp = $db->fetchAll($query);
                    foreach($resp as $res){
                        if($res['PLR_ALT'] == $res['PLG_ALT']){ 
                            $qtd_acerto++;
                        }                                
                    }

                    // total de questões por disciplina
                    
                    $query = 'SELECT count(*) as TOTAL ' 
                            .' FROM PUBLICACAO_LISTA_RESPOSTA r, MATRICULA m '
                            .' WHERE r.ALU_ID = m.ALU_ID AND m.MAT_STA = 1 AND m.CUR_ID = '.$alu['CUR_ID']
                            .' AND PUL_ID = '.$alu['PUL_ID']
                            .' AND DCP_ID = '.$gab['DCP_ID'];
                    $tot = $db->fetchRow($query);

                    $porc = 0;
                    if($tot['TOTAL'] > 0){
                        $porc = ($qtd_acerto * 100) / $tot['TOTAL'];
                        $porc = tools::trunc_valor($porc);
                    }

                    $porc = tools::str_pad_left($porc);
                    $qtd_acerto = tools::str_pad_left($qtd_acerto); 
                    $tot['TOTAL'] = tools::str_pad_left($tot['TOTAL']);

                    $arr = array(
                        'PUL_ID'    => $alu['PUL_ID'],
                        'CUR_ID'    => $alu['CUR_ID'],
                        'DCP_ID'    => $gab['DCP_ID'],
                        'PQD_POR'   => $porc,
                        'PQD_TOT'   => $tot['TOTAL'],
                        'PQD_ACR'   => $qtd_acerto,
                    );
                    
                    try {
                        $db->insert('PUBLST_QSTDISCUR', $arr);
                    } catch (Exception $ex) {
                        
                    }
                    
                }

            }
        
            $db->update('PUBLICACAO_LISTA', array('PUL_STARELQSTDISCUR' => 1), 'PUL_ID = '.$row['PUL_ID']);
        }
        //echo 'Gerado com sucesso.';
        
    }
    
    public function questaodisciplinacursobase($PUL_ID) {
        
        Zend_Loader::loadClass('tools','library');
        
        $db = Zend_Db_Table_Abstract::getDefaultAdapter();
        
        $query = 'SELECT SQL_CACHE PUL_ID' 
                .' FROM PUBLICACAO_LISTA'
                .' WHERE PUL_STA = 2 AND PUL_STARELQSTDISCBS = 0 AND PUL_ID = '.$PUL_ID;
        $result = $db->fetchAll($query);
        foreach($result as $row){
            $query = 'SELECT SQL_CACHE p.PUL_ID, m.ALU_ID , cb.CBS_ID ' 
                    .' FROM PUBLICACAO_LISTA_ALUNO p, MATRICULA m, CURSO c, CURSOBASE cb '
                    .' WHERE p.ALU_ID = m.ALU_ID AND m.MAT_STA = 1 '
                    .' AND m.CUR_ID = c.CUR_ID AND c.CBS_ID = cb.CBS_ID '
                    .' AND p.PUL_ID = '.$PUL_ID
                    .' GROUP BY cb.CBS_ID ';
            $alunos = $db->fetchAll($query);
            foreach($alunos as $alu){
                // 180 linas
                $query = 'SELECT SQL_CACHE DCP_ID' 
                        .' FROM PUBLICACAO_LISTA_GABARITO '
                        .' WHERE PUL_ID = '.$alu['PUL_ID']
                        .' GROUP BY DCP_ID';
                $gabr = $db->fetchAll($query);
                foreach($gabr as $gab){
                    $qtd_acerto = 0;
                    $query = 'SELECT SQL_CACHE r.PLR_ALT, g.PLG_ALT '
                            . 'FROM PUBLICACAO_LISTA_RESPOSTA r,  PUBLICACAO_LISTA_GABARITO g, MATRICULA m, CURSO c, CURSOBASE cb '
                            .' WHERE r.PLG_ID = g.PLG_ID AND r.PUL_ID = g.PUL_ID '
                            .' AND r.PLR_QST = g.PLG_QST '
                            . 'AND r.ALU_ID = m.ALU_ID AND m.MAT_STA = 1 AND cb.CBS_ID = '.$alu['CBS_ID']
                            .' AND m.CUR_ID = c.CUR_ID AND c.CBS_ID = cb.CBS_ID '
                            .' AND r.PUL_ID = '.$alu['PUL_ID']
                            .' AND r.DCP_ID  = '.$gab['DCP_ID'];
                    $resp = $db->fetchAll($query);
                    foreach($resp as $res){
                        if($res['PLR_ALT'] == $res['PLG_ALT']){ 
                            $qtd_acerto++;
                        }                                
                    }

                    // total de questões por disciplina
                    $query = 'SELECT count(*) as TOTAL ' 
                            .' FROM PUBLICACAO_LISTA_RESPOSTA r, MATRICULA m, CURSO c, CURSOBASE cb '
                            .' WHERE r.ALU_ID = m.ALU_ID AND m.MAT_STA = 1 AND cb.CBS_ID = '.$alu['CBS_ID']
                            .' AND m.CUR_ID = c.CUR_ID AND c.CBS_ID = cb.CBS_ID '
                            .' AND PUL_ID = '.$alu['PUL_ID']
                            .' AND DCP_ID = '.$gab['DCP_ID'];
                    $tot = $db->fetchRow($query);

                    $porc = 0;
                    if($tot['TOTAL'] > 0){
                        $porc = ($qtd_acerto * 100) / $tot['TOTAL'];
                        $porc = tools::trunc_valor($porc);
                    }

                    $porc = tools::str_pad_left($porc);
                    $qtd_acerto = tools::str_pad_left($qtd_acerto); 
                    $tot['TOTAL'] = tools::str_pad_left($tot['TOTAL']);

                    $arr = array(
                        'PUL_ID'    => $alu['PUL_ID'],
                        'CBS_ID'    => $alu['CBS_ID'],
                        'DCP_ID'    => $gab['DCP_ID'],
                        'PQD_POR'   => $porc,
                        'PQD_TOT'   => $tot['TOTAL'],
                        'PQD_ACR'   => $qtd_acerto,
                    );

                    try {
                        $db->insert('PUBLST_QSTDISCBS', $arr);
                    } catch (Exception $ex) {
                        
                    }
                }

            }
        
            $db->update('PUBLICACAO_LISTA', array('PUL_STARELQSTDISCBS' => 1), 'PUL_ID = '.$row['PUL_ID']);
            
        }
        //echo 'Gerado com sucesso.';
        
    }
    
    public function gerarrelatoriolistaAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();
        
        Zend_Loader::loadClass('tools','library');
        
        $db = Zend_Db_Table_Abstract::getDefaultAdapter();
        
        // Verifica e faz fechamento de listas
        $query = 'SELECT SQL_CACHE PUL_ID, PUL_DATENC, PUL_HORENC, PUL_STA FROM PUBLICACAO_LISTA '
                .' WHERE PUL_ANO = '.date('Y').' AND PUL_STA = 1';
        $result = $db->fetchAll($query);
        foreach($result as $row){
            if(strtotime($row['PUL_DATENC']) < strtotime(date('Y-m-d'))){
                $db->update('PUBLICACAO_LISTA',array('PUL_STA' => 1),'PUL_ID = '.$row['PUL_ID']);
            }
        }
        
        
        $query = 'SELECT SQL_CACHE PUL_ID' 
                .' FROM PUBLICACAO_LISTA'
                .' WHERE PUL_STA = 2 AND PUL_STAREL = 0';
        $result = $db->fetchAll($query);
        foreach($result as $row){
            
            $this->questaodisciplina($row['PUL_ID']);
            $this->rankaluno($row['PUL_ID']);
            $this->questaodisciplinaaluno($row['PUL_ID']);
            $this->questaodisciplinacurso($row['PUL_ID']);
            $this->questaodisciplinacursobase($row['PUL_ID']);
            
            $db->update('PUBLICACAO_LISTA', array('PUL_STAREL' => 1), 'PUL_ID = '.$row['PUL_ID']);
        }
        echo 'Gerado com sucesso.';
    }
    
    public function gerarrelatoriolistadisaluAction() {
        
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();
        
        Zend_Loader::loadClass('tools','library');
        
        $db = Zend_Db_Table_Abstract::getDefaultAdapter();
        
        $query = 'SELECT SQL_CACHE PUL_ID ' 
                .' FROM PUBLICACAO_LISTA '
                .' WHERE PUL_STA = 2 AND PUL_STAREL = 0 AND PUL_STARELQSTDISALU = 0';
        $result = $db->fetchAll($query);
        foreach($result as $row){
            $this->questaodisciplinaaluno($row['PUL_ID']);
        }
        echo 'Gerado com sucesso.';
    }
    
    
}

